import pymysql


class DataBase:
    def __init__(self):
        self.db = ['normal', 'light', 'status']
        info = ['127.0.0.1', 3306, 'root', 'lucky123']
        try:
            self.database = pymysql.connect(host=info[0], port=int(info[1]), user=info[2], password=info[3])
            self.cursor = self.database.cursor()
        except (IndexError, Exception):
            pass

    # 查询数据库连接状态
    def connect_mysql(self, info):
        try:
            self.database = pymysql.connect(host=info[0], port=int(info[1]), user=info[2], password=info[3])
            self.cursor = self.database.cursor()
            return True
        except (IndexError, Exception):
            return False

    # 查询当前地图结点数据
    def query_map_nodes(self):
        try:
            # 查询nodes
            self.database.select_db(self.db[0])
            # 构造sql查询语句
            sql = "SELECT * " \
                  "FROM normal.nodes " \
                  "ORDER BY nodes.id "
            self.cursor.execute(sql)
            # 获取查询结果
            data = self.cursor.fetchall()
            result = []
            for row, line in enumerate(data):
                result.append(list(line))
            return result
        except (IndexError, Exception):
            return []

    # 查询当前地图道路数据
    def query_map_roads(self):
        try:
            # 查询nodes
            self.database.select_db(self.db[0])
            # 构造sql查询语句
            sql = "SELECT * " \
                  "FROM normal.roads " \
                  "ORDER BY roads.id "
            self.cursor.execute(sql)
            # 获取查询结果
            data = self.cursor.fetchall()
            result = []
            for row, line in enumerate(data):
                result.append(list(line))
            return result
        except (IndexError, Exception):
            return []

    # 查询道路拥堵程度阈值
    def query_map_threshold(self):
        try:
            # 查询threshold
            self.database.select_db(self.db[0])
            # 构造sql查询语句
            sql = "SELECT * " \
                  "FROM normal.threshold " \
                  "ORDER BY threshold.road_id "
            self.cursor.execute(sql)
            # 获取查询结果
            data = self.cursor.fetchall()
            result = []
            for row, line in enumerate(data):
                result.append(list(line))
            return result
        except (IndexError, Exception):
            return []

    # 查询交通状态
    def query_traffic_status(self, info):
        try:
            date = info[0]
            time = info[1]
            area = info[2]
            cross = info[3]
            road = info[4]
            # 查询roads_id
            self.database.select_db(self.db[0])
            if area != 0:
                condition1 = "roads.area = %s" % int(area)
            else:
                condition1 = "roads.area"
            if cross != 0:
                condition2 = "roads.cross = %s" % int(cross)
            else:
                condition2 = "roads.cross"
            if road != 0:
                condition3 = "roads.id = %s" % int(road)
            else:
                condition3 = "roads.id"
            condition = condition1 + " and " + condition2 + " and " + condition3
            # 构造sql查询语句
            sql = "SELECT roads.id, roads.area, roads.cross " \
                  "FROM normal.roads " \
                  "WHERE %s " \
                  % condition
            self.cursor.execute(sql)
            # 获取查询结果
            data = self.cursor.fetchall()
            roads_id = []
            for row, line in enumerate(data):
                roads_id.append([line[0], line[1], line[2]])
            # 查询time_id
            self.database.select_db(self.db[0])
            # 构造sql查询语句
            sql = "SELECT time.id " \
                  "FROM normal.time " \
                  "WHERE time.time = '%s'" \
                  % time
            self.cursor.execute(sql)
            # 获取查询结果
            data = self.cursor.fetchall()
            time_id = data[0][0]
            # 查询交通状态flow、queue、delay
            self.database.select_db(self.db[2])
            result = []
            # 构造sql查询语句
            for row, line in enumerate(roads_id):
                sql = "SELECT * " \
                      "FROM status.status_%s " \
                      "WHERE status_%s.time_id = %s and status_%s.road_id = %s " \
                      % (date, date, int(time_id), date, int(line[0]))
                self.cursor.execute(sql)
                # 获取查询结果
                data = self.cursor.fetchall()
                for row2, line2 in enumerate(data):
                    result.append([line[1], line[2], line[0], line2[3], line2[4], line2[5]])
            return result
        except (IndexError, Exception):
            return []

    # 查询工作状态
    def query_work_status(self, info):
        try:
            data = self.query_traffic_status(info)
            result = []
            # 判断并组合工作状态数据
            for row, line in enumerate(data):
                if line[-3] is not None and line[-2] is not None and line[-1] is not None:
                    result.append([line[0], line[1], line[2], "正常"])
                else:
                    result.append([line[0], line[1], line[2], "异常"])
            return result
        except (IndexError, Exception):
            return []

    # 查询道路车流量
    def query_roads_flow(self, info):
        try:
            data = self.query_traffic_status(info)
            result = []
            for row, line in enumerate(data):
                result.append([line[2], line[3]])
            result.sort()
            return result
        except (IndexError, Exception):
            return []

    # 查询当前倒计时
    def query_count_down_time(self, info):
        try:
            area = info[0]
            cross = info[1]
            phase = info[2]
            # 查询nodes_id
            self.database.select_db(self.db[0])
            condition1 = "nodes.area = %s" % int(area)
            condition2 = "nodes.cross = %s" % int(cross)
            condition = condition1 + " and " + condition2
            # 构造sql查询语句
            sql = "SELECT nodes.id " \
                  "FROM normal.nodes " \
                  "WHERE %s " \
                  % condition
            self.cursor.execute(sql)
            # 获取查询结果
            data = self.cursor.fetchall()
            nodes_id = []
            for row, line in enumerate(data):
                nodes_id.append([line[0]])
            # 查询当前倒计时r、g、y
            self.database.select_db(self.db[1])
            # 构造sql查询语句
            result = []
            for row, line in enumerate(nodes_id):
                condition1 = "light.nodes_id = %s" % int(line[0])
                condition2 = "light.phase = %s" % int(phase)
                condition = condition1 + " and " + condition2
                sql = "SELECT light.r, light.g, light.y " \
                      "FROM light.light " \
                      "WHERE %s " \
                      % condition
                self.cursor.execute(sql)
                # 获取查询结果
                data = self.cursor.fetchall()
                for row2, line2 in enumerate(data):
                    result.append([line2[0], line2[1], line2[2]])
            return result
        except (IndexError, Exception):
            return []
